<!DOCTYPE html>
<html lang=zh>
<head>
    <meta charset="utf-8">
    
    <title>5-mysql-sql调优思路 | johnny技术地</title>
    
    
        <meta name="keywords" content="mysql" />
    
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />
    <meta name="description" content="sql调优思路1 优化表结构。 优化查询。从左到右。 索引优化  1.表结构检查表&amp;列名关键字（不区分大小写）在数据库设计建模阶段，对表名及字段名设置要合理，不能使用MySQL的关键字，如desc, order, status, group等。同时建议设置lower_case_table_names &#x3D; 1表名不区分大小写。 AUTO_INCREMENT属性建表的时候主键id带有AUTO_">
<meta property="og:type" content="article">
<meta property="og:title" content="5-mysql-sql调优思路">
<meta property="og:url" content="https://abcd_1101.gitee.io/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/5-mysql-sql%E8%B0%83%E4%BC%98%E6%80%9D%E8%B7%AF/index.html">
<meta property="og:site_name" content="johnny技术地">
<meta property="og:description" content="sql调优思路1 优化表结构。 优化查询。从左到右。 索引优化  1.表结构检查表&amp;列名关键字（不区分大小写）在数据库设计建模阶段，对表名及字段名设置要合理，不能使用MySQL的关键字，如desc, order, status, group等。同时建议设置lower_case_table_names &#x3D; 1表名不区分大小写。 AUTO_INCREMENT属性建表的时候主键id带有AUTO_">
<meta property="og:locale">
<meta property="og:image" content="https://abcd_1101.gitee.io/gitee-internal-blog/images/Mysql-%E8%B0%83%E4%BC%98%E8%84%91%E5%9B%BE.jpg">
<meta property="article:published_time" content="2021-09-04T08:06:22.000Z">
<meta property="article:modified_time" content="2021-10-02T03:21:51.921Z">
<meta property="article:author" content="Johnny">
<meta property="article:tag" content="mysql">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://abcd_1101.gitee.io/gitee-internal-blog/images/Mysql-%E8%B0%83%E4%BC%98%E8%84%91%E5%9B%BE.jpg">
    

    
        <link rel="alternate" href="/atom.xml" title="johnny技术地" type="application/atom+xml" />
    

    
        <link rel="icon" href="/gitee-internal-blog/favicon.ico" />
    

    
<link rel="stylesheet" href="/gitee-internal-blog/libs/font-awesome/css/font-awesome.min.css">

    
<link rel="stylesheet" href="/gitee-internal-blog/libs/open-sans/styles.css">

    
<link rel="stylesheet" href="/gitee-internal-blog/libs/source-code-pro/styles.css">


    
<link rel="stylesheet" href="/gitee-internal-blog/css/style.css">

    
<script src="/gitee-internal-blog/libs/jquery/2.1.3/jquery.min.js"></script>

    
<script src="/gitee-internal-blog/libs/jquery/plugins/cookie/1.4.1/jquery.cookie.js"></script>

    
    
        
<link rel="stylesheet" href="/gitee-internal-blog/libs/lightgallery/css/lightgallery.min.css">

    
    
        
<link rel="stylesheet" href="/gitee-internal-blog/libs/justified-gallery/justifiedGallery.min.css">

    
    
    
    


    
        <script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    
<meta name="generator" content="Hexo 5.4.0"></head>

<body>
    <div id="container">
        <header id="header">
    <div id="header-main" class="header-inner">
        <div class="outer">
            <a href="/gitee-internal-blog/" id="logo">
                <i class="logo"></i>
                <span class="site-title">johnny技术地</span>
            </a>
            <nav id="main-nav">
                
                    <a class="main-nav-link" href="/gitee-internal-blog/">首页</a>
                
                    <a class="main-nav-link" href="/gitee-internal-blog/archives">归档</a>
                
                    <a class="main-nav-link" href="/gitee-internal-blog/categories">分类</a>
                
                    <a class="main-nav-link" href="/gitee-internal-blog/tags">标签</a>
                
                    <a class="main-nav-link" href="/gitee-internal-blog/about">关于</a>
                
            </nav>
            
            <div id="search-form-wrap">

    <form class="search-form">
        <input type="text" class="ins-search-input search-form-input" placeholder="Search" />
        <button type="submit" class="search-form-submit"></button>
    </form>
    <div class="ins-search">
    <div class="ins-search-mask"></div>
    <div class="ins-search-container">
        <div class="ins-input-wrapper">
            <input type="text" class="ins-search-input" placeholder="Type something..." />
            <span class="ins-close ins-selectable"><i class="fa fa-times-circle"></i></span>
        </div>
        <div class="ins-section-wrapper">
            <div class="ins-section-container"></div>
        </div>
    </div>
</div>
<script>
(function (window) {
    var INSIGHT_CONFIG = {
        TRANSLATION: {
            POSTS: 'Posts',
            PAGES: 'Pages',
            CATEGORIES: 'Categories',
            TAGS: 'Tags',
            UNTITLED: '(Untitled)',
        },
        ROOT_URL: '/gitee-internal-blog/',
        CONTENT_URL: '/gitee-internal-blog/content.json',
    };
    window.INSIGHT_CONFIG = INSIGHT_CONFIG;
})(window);
</script>

<script src="/gitee-internal-blog/js/insight.js"></script>


</div>
        </div>
    </div>
    <div id="main-nav-mobile" class="header-sub header-inner">
        <table class="menu outer">
            <tr>
                
                    <td><a class="main-nav-link" href="/gitee-internal-blog/">首页</a></td>
                
                    <td><a class="main-nav-link" href="/gitee-internal-blog/archives">归档</a></td>
                
                    <td><a class="main-nav-link" href="/gitee-internal-blog/categories">分类</a></td>
                
                    <td><a class="main-nav-link" href="/gitee-internal-blog/tags">标签</a></td>
                
                    <td><a class="main-nav-link" href="/gitee-internal-blog/about">关于</a></td>
                
                <td>
                    
    <div class="search-form">
        <input type="text" class="ins-search-input search-form-input" placeholder="Search" />
    </div>

                </td>
            </tr>
        </table>
    </div>
</header>

        <div class="outer">
            
            
                <aside id="sidebar">
   
        
    <div class="widget-wrap" id='categories'>
        <h3 class="widget-title">
            <span>categories</span>
            &nbsp;
            <a id='allExpand' href="#">
                <i class="fa fa-angle-double-down fa-2x"></i>
            </a>
        </h3>
        
        
        
         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            DLT
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            Ledger
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            Corda
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2023/03/09/DLT/Ledger/Corda/Corda%E4%BE%8B%E5%AD%90/">Corda例子</a></li>  </ul> 
                    </li> 
                     </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            概念和框架
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2023/02/04/DLT/%E6%A6%82%E5%BF%B5%E5%92%8C%E6%A1%86%E6%9E%B6/%E5%8C%BA%E5%9D%97%E9%93%BE%E7%90%86%E8%AE%BA%E5%9F%BA%E7%A1%80/">区块链理论基础</a></li>  </ul> 
                    </li> 
                     </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            IDE
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/05/03/IDE-eclipse-debug-f5-8/">IDE eclipse debug f5-8</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            docker_k8s
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/06/19/docker_k8s/springcloud%20vs%20k8s/"></a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/06/docker_k8s/%E5%AE%B9%E5%99%A8-docker-k8s/">容器-docker-k8s</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            git
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/04/05/git-reset-and-revert/">git reset and revert</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/04/14/git-pull-request-to-contribute-other-project/">Pull request to contribute other project</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/04/18/git-stash%E7%94%A8%E6%B3%95/">git stash用法</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/05/16/git-tag%E8%AE%B0%E5%BD%95%E7%89%88%E6%9C%AC%E5%AF%B9%E5%BA%94commit/">git tag记录版本对应commit</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            java
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/04/25/java%E5%9F%BA%E7%A1%801/">java基础1</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            jvm
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            性能监测
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/04/11/jvisualvm%E5%92%8Csprintboot%20admin%E6%80%A7%E8%83%BD%E7%9B%91%E6%B5%8B/">jvisualvm和sprintboot admin性能监测</a></li>  </ul> 
                    </li> 
                     </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            linux
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            常用命令
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/04/11/linux%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/">linux常用命令</a></li>  </ul> 
                    </li> 
                     <li class="file"><a href="/gitee-internal-blog/2021/04/18/linux-find%E5%91%BD%E4%BB%A4/">linux-find命令</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            spring
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/05/01/spring-log%E6%97%A5%E5%BF%97%E8%AE%B0%E5%BD%95/">日志记录</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/05/03/spring-mvc%E5%BF%85%E5%A4%87%E9%85%8D%E7%BD%AE/">spring mvc必备配置</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/05/23/spring%E8%84%9A%E6%89%8B%E6%9E%B6%E5%8E%9F%E7%90%86/">spring脚手架原理</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            windows
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/04/17/windows-dns%E6%9C%8D%E5%8A%A1%E5%99%A8%E6%9C%AA%E5%93%8D%E5%BA%94/">windows dns服务器未响应</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            中间件
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            mq
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/06/25/%E4%B8%AD%E9%97%B4%E4%BB%B6/mq/2-mq%E9%87%8D%E5%A4%8D%E9%A1%BA%E5%BA%8F%E6%B6%88%E8%B4%B9/">2-mq重复顺序消费</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E4%B8%AD%E9%97%B4%E4%BB%B6/mq/1-mq%E5%9F%BA%E7%A1%80/">1-mq基础</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E4%B8%AD%E9%97%B4%E4%BB%B6/mq/0-mq-zookeeper%E6%98%AF%E4%BB%80%E4%B9%88/">0-mq-zookeeper是什么</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E4%B8%AD%E9%97%B4%E4%BB%B6/mq/3-mq-kafka%E5%85%A5%E9%97%A8/">3-mq-kafka入门</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E4%B8%AD%E9%97%B4%E4%BB%B6/mq/4-mq%E9%97%AE%E9%A2%98%E4%B8%8D%E4%B8%A2%E5%A4%B1-%E9%87%8D%E5%A4%8D-%E6%9C%89%E5%BA%8F-%E5%A0%86%E7%A7%AF/">4-mq问题不丢失_重复_有序_堆积</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/05/%E4%B8%AD%E9%97%B4%E4%BB%B6/mq/5-mq-kafka-segement%E8%AF%BB%E5%86%99/">5-mq-kafka-segement读写</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/07/%E4%B8%AD%E9%97%B4%E4%BB%B6/mq/6-mq-kafka%E4%B8%89%E8%BF%9E%E5%87%BB/">6-mq-kafka三连击</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/12/%E4%B8%AD%E9%97%B4%E4%BB%B6/mq/7-mq-kafka%E6%98%AF%E6%8E%A8%E8%BF%98%E6%98%AF%E6%8B%89/">7-mq-kafka是推还是拉</a></li>  </ul> 
                    </li> 
                     </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            必会框架
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/09/06/%E5%BF%85%E4%BC%9A%E6%A1%86%E6%9E%B6/%E6%A1%86%E6%9E%B6-%E9%AB%98%E6%80%A7%E8%83%BDNIO-Netty/">框架-高性能NIO-Netty</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            操作系统
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/09/06/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F-pageCache/">操作系统-pageCache</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/06/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F-nioFilechannel%E5%A0%86%E5%A4%96directBuffer%E4%B8%8E%E5%A0%86%E5%86%85byteBuffer/">操作系统-nioFilechannel堆外directBuffer与堆内byteBufferbyteBuffer</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/06/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F-%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C%E4%BC%98%E5%8C%96%E6%8F%A1%E6%89%8B%E6%8C%A5%E6%89%8B/">操作系统-计算机网络优化握手挥手</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/06/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F-%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C%E5%B8%B8%E9%97%AE/">操作系统-计算机网络常问</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/06/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F-%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C%E6%8A%93%E5%8C%85/">操作系统-计算机网络抓包</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory open">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder-open"></i>
                            &nbsp;
                            数据库
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory open">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder-open"></i>
                            &nbsp;
                            mysql
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/0-mysql%E7%BB%93%E6%9E%84%E4%B8%8E%E6%8F%92%E5%85%A5/">0-mysql结构与插入</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/1-mysql%E4%B8%80%E8%87%B4%E6%80%A7%E8%AF%BB-%E4%BA%8B%E5%8A%A1%E5%9B%9E%E6%BB%9A-mvcc/">1-mysql一致性读-事务回滚-mvcc</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/3-mysql-sql%E6%89%A7%E8%A1%8C%E8%BF%87%E7%A8%8B%E5%AE%8F%E8%A7%82%E6%B5%81%E7%A8%8B-redo-undo-bin/">3-mysql-sql执行过程宏观流程-redo-undo-bin</a></li>  <li class="file active"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/5-mysql-sql%E8%B0%83%E4%BC%98%E6%80%9D%E8%B7%AF/">5-mysql-sql调优思路</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/4-mysql-explain%E6%89%A7%E8%A1%8C%E5%85%B7%E4%BD%93%E6%B5%81%E7%A8%8B/">4-mysql-explain执行具体流程</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/6-mysql-%E7%B4%A2%E5%BC%95%E4%BD%9C%E7%94%A8%E5%92%8C%E8%AE%BE%E8%AE%A1/">6-mysql-索引作用和设计</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/8-mysql-%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0%E8%AE%BE%E5%A4%9A%E5%A4%A7/">8-mysql-数据库连接池设多大</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/9-mysql-%E4%BB%8E%E4%B8%9A%E5%8A%A1%E5%88%B0%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1-%E8%BD%AF%E4%BB%B6%E5%B7%A5%E7%A8%8B/">9-mysql-从业务到数据库设计-软件工程</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/7-mysql-changebuffer-merge/">7-mysql-changebuffer-merge</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/2-mysql-undo-purge_bin_redo-flush%E8%84%8F%E9%A1%B5/">2-mysql-undo-purge_bin_redo-flush脏页</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/10-mysql-mysql8%E6%96%B0%E7%89%B9%E6%80%A7/">10-mysql-mysql8新特性</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/12-mysql-%E5%85%A8%E5%B1%80%E9%94%81-%E8%A1%A8%E9%94%81-%E8%A1%8C%E9%94%81/">12-mysql-全局锁-表锁-行锁</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/11-mysql-%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6%E5%92%8C%E5%AE%9E%E6%88%98/">11-mysql-主从复制和实战</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/13-mysql-%E6%9E%B6%E6%9E%84%E6%96%B9%E6%A1%88/">13-mysql-架构方案</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            redis
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/8-redis-%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E5%92%8C%E6%93%8D%E4%BD%9C/">8-redis-数据结构和操作</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/11-redis-%E5%8F%AF%E7%94%A8%E6%80%A7-mysql%E4%B8%80%E8%87%B4%E6%80%A7%E9%97%AE%E9%A2%98/">11-redis-可用性-mysql一致性问题</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/1-redis-%E5%A5%BD-%E4%BD%BF%E7%94%A8%E5%9C%BA%E6%99%AF/">1-redis-好-使用场景</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/2-redis-%E5%BF%AB-%E5%86%85%E5%AD%98%E8%80%97%E5%B0%BD%E5%90%8E%E4%BC%9A%E6%80%8E%E6%A0%B7/">2-redis-快-内存耗尽后会怎样</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/3-redis-%E5%BF%AB-%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E5%92%8C%E6%93%8D%E4%BD%9C/">3-redis-快-数据结构和操作</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/4-redis-%E5%BF%AB-%E4%B8%BA%E4%BD%95%E5%8F%98%E5%A4%9A%E7%BA%BF%E7%A8%8B/">4-redis-快-为何变多线程</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/7-redis-%E4%BC%98%E5%8C%96-%E6%9C%80%E4%BD%B3%E5%AE%9E%E8%B7%B5/"></a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/8-redis-%E4%BC%98%E5%8C%96-%E9%9B%AA%E5%B4%A9-%E7%A9%BF%E9%80%8F-%E5%87%BB%E7%A9%BF/">8-redis-优化-雪崩-穿透-击穿</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/9-redis-%E5%8F%AF%E7%94%A8%E6%80%A7-%E6%8C%81%E4%B9%85%E5%8C%96/">9-redis-可用性-持久化</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/20-redis-%E9%9D%A2%E8%AF%95%E9%A2%98/">20-redis-面试题</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/5-redis-%E5%BF%AB-%E5%A4%9A%E8%B7%AFio%E5%A4%8D%E7%94%A8/">5-redis-快-多路io复用</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/6-redis-%E4%BC%98%E5%8C%96-%E5%8F%98%E6%85%A2%E4%BA%86%E5%90%97/">6-redis-优化-变慢了吗</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/redis/10-redis-%E5%8F%AF%E7%94%A8%E6%80%A7-%E9%9B%86%E7%BE%A4%E7%AF%87/">10-redis-可用性-集群篇</a></li>  </ul> 
                    </li> 
                     </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            知识点框架
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            19-数据库
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/08/02/19-1-Oracle%E5%B8%B8%E8%80%83/">19-1-Oracle常考</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            2-jvm
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/06/25/2-1-JVM%E8%84%91%E5%9B%BE/">2-1-JVM脑图</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/06/25/2-2-JVM%E5%86%85%E5%AD%98%E5%8C%BA%E5%9F%9F/">2-2-JVM内存区域</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/06/26/2-3-jvm%E8%BF%90%E8%A1%8C%E6%97%B6%E5%86%85%E5%AD%98/">2-3-jvm运行时内存</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/06/26/2-4-%E5%9E%83%E5%9C%BE%E5%9B%9E%E6%94%B6-%E7%AE%97%E6%B3%95-%E9%80%83%E9%80%B8%E5%88%86%E6%9E%90/">2-4-垃圾回收-算法-逃逸分析</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/06/27/2-5-java%E5%9B%9B%E7%A7%8D%E5%BC%95%E7%94%A8%E7%B1%BB%E5%9E%8B/">2-5-java四种引用类型</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/06/28/2-6-7-gc%E7%AE%97%E6%B3%95%E5%92%8C%E5%9E%83%E5%9C%BE%E6%94%B6%E9%9B%86%E5%99%A8/">2-6-7-gc算法和垃圾收集器</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/06/28/2-8-java-io-and-nio/">2-8-java io and nio</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/06/29/2-9-jvm%E7%B1%BB%E5%8A%A0%E8%BD%BD%E6%9C%BA%E5%88%B6/">2-9-jvm类加载机制</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            3-java集合
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/06/30/3-1-java%E6%8E%A5%E5%8F%A3%E7%BB%A7%E6%89%BF%E5%85%B3%E7%B3%BB%E4%B8%8E%E5%AE%9E%E7%8E%B0/">3-1-java接口继承关系与实现</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/06/30/3-2-List/">3-2-List</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/01/3-3-Set/">3-3-Set</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/01/3-4-Map%E6%98%A0%E5%B0%84/">3-4-Map映射</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            4-java多线程
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/07/02/4-1-2-%E5%A4%9A%E7%BA%BF%E7%A8%8B%E8%84%91%E5%9B%BE%E5%92%8C%E5%9B%9B%E7%A7%8D%E7%BA%BF%E7%A8%8B%E5%88%9B%E5%BB%BA%E6%96%B9%E6%B3%95/">4-1-2-多线程脑图和四种线程创建</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/02/4-3-4%E7%A7%8D%E7%BA%BF%E7%A8%8B%E6%B1%A0/">4-3-4种线程池</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/02/4-4-%E7%BA%BF%E7%A8%8B%E7%94%9F%E5%91%BD%E5%91%A8%E6%9C%9F5%E7%A7%8D%E7%8A%B6%E6%80%81/">4-4-线程生命周期5种状态</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/02/4-5-%E7%BB%88%E6%AD%A2%E7%BA%BF%E7%A8%8B4%E6%96%B9%E5%BC%8F/">4-5-终止线程4方式</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/02/4-6-7-sleep%E4%B8%8Ewait-start%E4%B8%8Erun/">4-6-7-sleep与wait-start与run</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/03/4-8-java%E5%90%8E%E5%8F%B0%E7%BA%BF%E7%A8%8B/">4-8-java后台线程</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/03/4-9-java%E9%94%81/">4-9-java锁</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/05/4-10-%E7%BA%BF%E7%A8%8B%E5%9F%BA%E6%9C%AC%E6%96%B9%E6%B3%95/">4-10-线程基本方法</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/06/4-11-%E7%BA%BF%E7%A8%8B%E4%B8%8A%E4%B8%8B%E6%96%87%E5%88%87%E6%8D%A2/">4-11-线程上下文切换</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/06/4-12-%E5%90%8C%E6%AD%A5%E9%94%81%E4%B8%8E%E6%AD%BB%E9%94%81/">4-12-同步锁与死锁</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/06/4-13-%E7%BA%BF%E7%A8%8B%E6%B1%A0%E5%8E%9F%E7%90%86/">4-13-线程池原理</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/08/4-14-%E9%98%BB%E5%A1%9E%E9%98%9F%E5%88%97/">4-14-阻塞队列</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/10/4-15-cyclicBarrier-countdownlatch-semaphore/">4-15-cyclicBarrier-countdownlatch-semaphore</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/14/4-16-volatile%E5%85%B3%E9%94%AE%E5%AD%97%E4%BD%9C%E7%94%A8/">4-16-volatile关键字作用</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/14/4-17-%E4%B8%A4%E4%B8%AA%E7%BA%BF%E7%A8%8B%E5%85%B1%E4%BA%AB%E6%95%B0%E6%8D%AE/">4-17-两个线程共享数据</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/16/4-18-ThreadLocal%E4%BD%9C%E7%94%A8/">4-18-ThreadLocal作用</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/16/4-19-synchronized%E5%92%8Creentrantlock%E5%8C%BA%E5%88%AB/">4-19-synchronized和reentrantlock区别</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/17/4-20-ConcurrentHashMap/">4-20-ConcurrentHashMap</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/17/4-21-22-java%E7%BA%BF%E7%A8%8B%E8%B0%83%E5%BA%A6%E5%92%8C%E7%AE%97%E6%B3%95/">4-21-22-java线程调度和算法</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/17/4-23-cas%E6%AF%94%E8%BE%83%E5%B9%B6%E4%BA%A4%E6%8D%A2/">4-23-cas比较并交换</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/17/4-24-aqs%E6%8A%BD%E8%B1%A1%E7%9A%84%E9%98%9F%E5%88%97%E5%90%8C%E6%AD%A5%E5%99%A8/">4-24-aqs抽象的队列同步器</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            5-java基础
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/07/19/5-1-exception%E5%BC%82%E5%B8%B8%E5%88%86%E7%B1%BB%E5%8F%8A%E5%A4%84%E7%90%86/">5-1-exception异常分类及处理</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/20/5-2-java%E5%8F%8D%E5%B0%84/">5-2-java反射</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/22/5-3-java%E6%B3%A8%E8%A7%A3annotation/">5-3-java注解annotation</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/24/5-4-java%E5%86%85%E9%83%A8%E7%B1%BB/">5-4-java内部类</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/08/14/5-5-jdk8-lambda/">5-5-jdk8-lambda</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/08/14/5-6-RxJava/">5-6-RxJava</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/08/14/5-7-java-Observer%E6%8E%A5%E5%8F%A3%E5%92%8CObservable%E7%B1%BB/">5-7-java-Observer接口和Observable类</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            6-spring
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/05/03/6-2-springboot%E5%B8%B8%E8%80%83/">6-2-springboot常考</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/31/6-1-spring%E5%B8%B8%E8%80%83/">6-1-spring常考</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/08/28/6-3-springcloud%E7%94%A8%E8%BF%87%E7%9A%84/">6-3-springcloud用过的</a></li>  </ul> 
                    </li> 
                     </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            面试的项目实现
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            中间件
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            es
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/06/25/%E4%B8%AD%E9%97%B4%E4%BB%B6/es/es/">es</a></li>  </ul> 
                    </li> 
                     </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            微服务
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/08/21/%E9%9D%A2%E8%AF%95%E7%9A%84%E9%A1%B9%E7%9B%AE%E5%AE%9E%E7%8E%B0/%E5%BE%AE%E6%9C%8D%E5%8A%A1/hystrix/"></a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            数据库
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            mysql
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/09/04/%E9%9D%A2%E8%AF%95%E7%9A%84%E9%A1%B9%E7%9B%AE%E5%AE%9E%E7%8E%B0/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/0-mysql%E7%BB%93%E6%9E%84%E4%B8%8E%E6%8F%92%E5%85%A5/">0-mysql结构与插入</a></li>  </ul> 
                    </li> 
                     </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            自动补全
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/06/25/%E9%9D%A2%E8%AF%95%E7%9A%84%E9%A1%B9%E7%9B%AE%E5%AE%9E%E7%8E%B0/%E8%87%AA%E5%8A%A8%E8%A1%A5%E5%85%A8/es%E8%87%AA%E5%8A%A8%E8%A1%A5%E5%85%A8/">es自动补全</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/08/21/%E9%9D%A2%E8%AF%95%E7%9A%84%E9%A1%B9%E7%9B%AE%E5%AE%9E%E7%8E%B0/%E8%87%AA%E5%8A%A8%E8%A1%A5%E5%85%A8/redis%E8%87%AA%E5%8A%A8%E8%A1%A5%E5%85%A8/"></a></li>  </ul> 
                    </li> 
                     <li class="file"><a href="/gitee-internal-blog/2021/06/25/%E9%9D%A2%E8%AF%95%E7%9A%84%E9%A1%B9%E7%9B%AE%E5%AE%9E%E7%8E%B0/IJP%E9%9D%A2%E8%AF%95%E9%A1%B9%E7%9B%AE%E5%AE%9E%E7%8E%B0/">IJP面试项目实现</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/06/25/%E9%9D%A2%E8%AF%95%E7%9A%84%E9%A1%B9%E7%9B%AE%E5%AE%9E%E7%8E%B0/Ex%E9%9D%A2%E8%AF%95%E9%A1%B9%E7%9B%AE%E5%AE%9E%E7%8E%B0/">Ex面试项目实现</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            面试题
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/06/25/%E9%9D%A2%E8%AF%95%E9%A2%98/SpringBoot+Tomcat+Nginx+Netty%E9%9D%A2%E8%AF%95%E9%A2%98/">SpringBoot+Tomcat+Nginx+Netty面试题</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/06/25/%E9%9D%A2%E8%AF%95%E9%A2%98/%E9%98%BF%E9%87%8C%E9%9D%A2%E8%AF%95%E4%BA%8C/">阿里面试题2</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/06/25/%E9%9D%A2%E8%AF%95%E9%A2%98/%E9%98%BF%E9%87%8C%E9%9D%A2%E8%AF%95%E4%B8%80/">阿里面试题1</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/08/16/%E9%9D%A2%E8%AF%95%E9%A2%98-java%E5%9F%BA%E7%A1%80%E9%9D%A2%E8%AF%9516%E9%97%AE/">面试题-java基础面试16问</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            项目经历
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            2019
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            xinge_notification
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/04/05/oracle_explain/">Oracle explain</a></li>  </ul> 
                    </li> 
                     </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            2021
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            房贷项目
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/04/05/%E9%A1%B9%E7%9B%AE%E4%BB%8E0%E5%88%B01/">项目从0到1</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/04/12/email-telnet-smtp-health-check/">email telnet smtp health check</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/04/12/%E5%8E%8B%E6%B5%8B%E5%8F%82%E6%95%B0%E6%8C%87%E6%A0%87/">压测参数指标</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/04/12/checkmarx%E5%AE%89%E5%85%A8%E6%89%AB%E6%8F%8F/">checkmarx安全扫描</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/04/25/db-table-annotation/">db table annotation</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/04/25/jdbctemplate-sql-injection/">jdbctemplate sql injection</a></li>  </ul> 
                    </li> 
                     </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            微信开发
                        </a>
                         <ul class="unstyled" id="tree" > 
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            安全
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/05/16/%E5%AE%89%E5%85%A8-java-ssl%E9%94%99%E8%AF%AF%E8%A7%A3%E5%86%B3%E7%A5%9E%E5%99%A8-net-debug%E5%8F%82%E6%95%B0/">安全-java ssl 错误解决神器 net debug参数</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/28/%E5%AE%89%E5%85%A8-%E5%8A%A0%E5%AF%86%E6%9C%BA/">安全-加密机</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            容器
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/07/28/%E6%9C%8D%E5%8A%A1%E5%99%A8/%E6%9C%8D%E5%8A%A1%E5%99%A8-IBM%20websphere%E7%BB%8F%E9%AA%8C%20-%20%E5%89%AF%E6%9C%AC/">服务器-IBM websphere经验</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/28/%E6%9C%8D%E5%8A%A1%E5%99%A8/%E6%9C%8D%E5%8A%A1%E5%99%A8-Tomcat%E7%BB%8F%E9%AA%8C/">服务器-Tomcat经验</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/07/28/%E6%9C%8D%E5%8A%A1%E5%99%A8/%E6%9C%8D%E5%8A%A1%E5%99%A8-IBM%20websphere%E7%BB%8F%E9%AA%8C/">服务器-IBM websphere经验</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/08/15/docker_k8s/%E5%AE%B9%E5%99%A8-k8s%E7%BD%91%E9%A1%B5%E5%B8%B8%E8%A7%81%E5%90%8D%E8%AF%8D/">容器-k8s网页常见名词</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/09/06/docker_k8s/%E5%AE%B9%E5%99%A8-Istio/">容器-Istio</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            小程序
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/05/16/%E5%BE%AE%E4%BF%A1%E5%B0%8F%E7%A8%8B%E5%BA%8FnavigateTo%E4%B8%8EredirectTo%E5%8C%BA%E5%88%AB/">小程序navigateTo与redirectTo区别</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/08/30/%E5%BE%AE%E4%BF%A1%E5%B0%8F%E7%A8%8B%E5%BA%8F%E7%9A%84%E9%85%8D%E7%BD%AE/">微信小程序的配置</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            手机抓包
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/04/10/Fiddler%E5%AF%B9%E5%AE%89%E5%8D%93%E7%A7%BB%E5%8A%A8%E7%AB%AF%E7%9A%84%E6%8A%93%E5%8C%85%E6%B5%8B%E8%AF%95/">Fiddler对安卓移动端的抓包测试</a></li>  </ul> 
                    </li> 
                    
                    <li class="directory">
                        <a href="#" data-role="directory">
                            <i class="fa fa-folder"></i>
                            &nbsp;
                            高级群发系统
                        </a>
                         <ul class="unstyled" id="tree" >  <li class="file"><a href="/gitee-internal-blog/2021/05/09/Oracle-clob%E5%AD%97%E6%AE%B5/">Oracle clob字段</a></li>  </ul> 
                    </li> 
                     <li class="file"><a href="/gitee-internal-blog/2021/04/18/%E9%A1%B9%E7%9B%AE%E6%80%A7%E8%83%BD%E5%8F%82%E6%95%B0/">项目性能参数</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/05/09/%E5%BE%AE%E4%BF%A1%E6%8E%A5%E5%8F%A3%E6%B3%A8%E6%84%8F%E7%82%B9%E6%B1%87%E9%9B%86/">微信接口注意点汇集</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/05/09/%E5%BE%AE%E4%BF%A1%E4%B8%93%E7%BA%BF%E7%9A%84%E7%BD%91%E7%BB%9C%E5%B1%82%E7%90%86%E8%A7%A3/">专线的网络层理解</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/05/15/%E5%BE%AE%E4%BF%A1Sprint-plan/">Sprint plan</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/05/15/%E5%BE%AE%E4%BF%A1%E4%B8%8E%E9%93%B6%E8%81%94%E7%9A%84sso%E5%8D%95%E7%82%B9%E7%99%BB%E5%BD%95/">微信与银联的sso单点登录</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/05/22/G3%E8%87%AA%E5%8A%A8%E5%8C%96/">G3自动化</a></li>  <li class="file"><a href="/gitee-internal-blog/2021/05/29/%E5%BE%AE%E4%BF%A1%E9%A1%B9%E7%9B%AE%E5%BE%80ikp%E6%90%AC/">微信项目往ikp搬</a></li>  </ul> 
                    </li> 
                     </ul> 
                    </li> 
                     </ul> 
    </div>
    <script>
        $(document).ready(function() {
            var iconFolderOpenClass  = 'fa-folder-open';
            var iconFolderCloseClass = 'fa-folder';
            var iconAllExpandClass = 'fa-angle-double-down';
            var iconAllPackClass = 'fa-angle-double-up';
            // Handle directory-tree expansion:
            // 左键单独展开目录
            $(document).on('click', '#categories a[data-role="directory"]', function (event) {
                event.preventDefault();

                var icon = $(this).children('.fa');
                var expanded = icon.hasClass(iconFolderOpenClass);
                var subtree = $(this).siblings('ul');
                icon.removeClass(iconFolderOpenClass).removeClass(iconFolderCloseClass);
                if (expanded) {
                    if (typeof subtree != 'undefined') {
                        subtree.slideUp({ duration: 100 });
                    }
                    icon.addClass(iconFolderCloseClass);
                } else {
                    if (typeof subtree != 'undefined') {
                        subtree.slideDown({ duration: 100 });
                    }
                    icon.addClass(iconFolderOpenClass);
                }
            });
            // 右键展开下属所有目录
            $('#categories a[data-role="directory"]').bind("contextmenu", function(event){
                event.preventDefault();
                
                var icon = $(this).children('.fa');
                var expanded = icon.hasClass(iconFolderOpenClass);
                var listNode = $(this).siblings('ul');
                var subtrees = $.merge(listNode.find('li ul'), listNode);
                var icons = $.merge(listNode.find('.fa'), icon);
                icons.removeClass(iconFolderOpenClass).removeClass(iconFolderCloseClass);
                if(expanded) {
                    subtrees.slideUp({ duration: 100 });
                    icons.addClass(iconFolderCloseClass);
                } else {
                    subtrees.slideDown({ duration: 100 });
                    icons.addClass(iconFolderOpenClass);
                }
            })
            // 展开关闭所有目录按钮
            $(document).on('click', '#allExpand', function (event) {
                event.preventDefault();
                
                var icon = $(this).children('.fa');
                var expanded = icon.hasClass(iconAllExpandClass);
                icon.removeClass(iconAllExpandClass).removeClass(iconAllPackClass);
                if(expanded) {
                    $('#sidebar .fa.fa-folder').removeClass('fa-folder').addClass('fa-folder-open')
                    $('#categories li ul').slideDown({ duration: 100 });
                    icon.addClass(iconAllPackClass);
                } else {
                    $('#sidebar .fa.fa-folder-open').removeClass('fa-folder-open').addClass('fa-folder')
                    $('#categories li ul').slideUp({ duration: 100 });
                    icon.addClass(iconAllExpandClass);
                }
            });  
        });
    </script>

    
    <div id="toTop" class="fa fa-angle-up"></div>
</aside>
            
            <section id="main"><article id="post-数据库/mysql/5-mysql-sql调优思路" class="article article-type-post" itemscope itemprop="blogPost">
    <div class="article-inner">
        
        
            <header class="article-header">
                
                    <div class="article-meta">
                        
    <div class="article-category">
    	<i class="fa fa-folder"></i>
        <a class="article-category-link" href="/gitee-internal-blog/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a><i class="fa fa-angle-right"></i><a class="article-category-link" href="/gitee-internal-blog/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/">mysql</a>
    </div>

                        
    <div class="article-tag">
        <i class="fa fa-tag"></i>
        <a class="tag-link-link" href="/gitee-internal-blog/tags/mysql/" rel="tag">mysql</a>
    </div>

                        
    <div class="article-date">
        <i class="fa fa-calendar"></i>
        <a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/5-mysql-sql%E8%B0%83%E4%BC%98%E6%80%9D%E8%B7%AF/">
            <time datetime="2021-09-04T08:06:22.000Z" itemprop="datePublished">2021-09-04</time>
        </a>
    </div>


                        
                            <i class="fa fa-bar-chart"></i>
                            <span id="busuanzi_container_site_pv"><span id="busuanzi_value_page_pv"></span></span>    
                        
                        
                            <div class="article-meta-button">
                                <a target="_blank" rel="noopener" href='https://github.com/zthxxx/Wiki-site/raw/writing/source/_posts/数据库/mysql/5-mysql-sql调优思路.md'> Source </a>
                            </div>
                            <div class="article-meta-button">
                                <a target="_blank" rel="noopener" href='https://github.com/zthxxx/Wiki-site/edit/writing/source/_posts/数据库/mysql/5-mysql-sql调优思路.md'> Edit </a>
                            </div>
                            <div class="article-meta-button">
                                <a target="_blank" rel="noopener" href='https://github.com/zthxxx/Wiki-site/commits/writing/source/_posts/数据库/mysql/5-mysql-sql调优思路.md'> History </a>
                            </div>
                        
                    </div>
                
                
    
        <h1 class="article-title" itemprop="name">
            5-mysql-sql调优思路
        </h1>
    

            </header>
        
        
        <div class="article-entry" itemprop="articleBody">
        
        
            
                <div id="toc" class="toc-article">
                <strong class="toc-title">Catalogue</strong>
                    <ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#sql%E8%B0%83%E4%BC%98%E6%80%9D%E8%B7%AF1"><span class="toc-number">1.</span> <span class="toc-text">sql调优思路1</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#1-%E8%A1%A8%E7%BB%93%E6%9E%84%E6%A3%80%E6%9F%A5"><span class="toc-number">1.1.</span> <span class="toc-text">1.表结构检查</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A1%A8-amp-%E5%88%97%E5%90%8D%E5%85%B3%E9%94%AE%E5%AD%97%EF%BC%88%E4%B8%8D%E5%8C%BA%E5%88%86%E5%A4%A7%E5%B0%8F%E5%86%99%EF%BC%89"><span class="toc-number">1.1.1.</span> <span class="toc-text">表&amp;列名关键字（不区分大小写）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#AUTO-INCREMENT%E5%B1%9E%E6%80%A7"><span class="toc-number">1.1.2.</span> <span class="toc-text">AUTO_INCREMENT属性</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#NOT-NULL%E5%B1%9E%E6%80%A7"><span class="toc-number">1.1.3.</span> <span class="toc-text">NOT NULL属性</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#DEFAULT%E5%B1%9E%E6%80%A7"><span class="toc-number">1.1.4.</span> <span class="toc-text">DEFAULT属性</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#TEXT%E7%B1%BB%E5%9E%8B%EF%BC%88varchar%E4%B8%8Echar%E5%8C%BA%E5%88%AB%EF%BC%89"><span class="toc-number">1.1.5.</span> <span class="toc-text">TEXT类型（varchar与char区别）</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-%E4%BC%98%E5%8C%96%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.2.</span> <span class="toc-text">2.优化查询</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#select%E6%A3%80%E6%9F%A5%EF%BC%88%E5%AD%97%E6%AE%B5%E5%87%BD%E6%95%B0%E4%B8%8D%E8%A6%81%EF%BC%89"><span class="toc-number">1.2.1.</span> <span class="toc-text">select检查（字段函数不要）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#from%E6%A3%80%E6%9F%A5-inner-join"><span class="toc-number">1.2.2.</span> <span class="toc-text">from检查(inner join)</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#where%E6%A3%80%E6%9F%A5-%E9%81%BF%E5%85%8D%E8%BF%90%E7%AE%97-or%EF%BC%9B%E7%B1%BB%E5%9E%8B%E8%BD%AC%E6%8D%A2%EF%BC%9B%E5%AD%97%E7%AC%A6%E9%9B%86"><span class="toc-number">1.2.3.</span> <span class="toc-text">where检查(避免运算, or；类型转换；字符集)</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#group-by%E6%A3%80%E6%9F%A5%EF%BC%88%E5%89%8D%E7%BC%80%E7%B4%A2%E5%BC%95%EF%BC%8C%E5%87%BD%E6%95%B0%E8%BF%90%E7%AE%97%E4%B8%8D%E6%B6%88%E9%99%A4%E6%8E%92%E5%BA%8F%EF%BC%89"><span class="toc-number">1.2.4.</span> <span class="toc-text">group by检查（前缀索引，函数运算不消除排序）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#order-by%E6%A3%80%E6%9F%A5%EF%BC%88%E5%89%8D%E7%BC%80%E7%B4%A2%E5%BC%95%EF%BC%8C%E8%A6%81%E7%94%A8%E7%B4%A2%E5%BC%95%E5%88%97%EF%BC%89"><span class="toc-number">1.2.5.</span> <span class="toc-text">order by检查（前缀索引，要用索引列）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#limit%E6%A3%80%E6%9F%A5%EF%BC%88%E5%88%A9%E7%94%A8%E5%A5%BD%E4%B8%BB%E9%94%AE%EF%BC%89"><span class="toc-number">1.2.6.</span> <span class="toc-text">limit检查（利用好主键）</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#3-%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96%EF%BC%88explain%EF%BC%89"><span class="toc-number">1.3.</span> <span class="toc-text">3.索引优化（explain）</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%BB%E9%94%AE%E7%B4%A2%E5%BC%95%E6%9B%B4%E6%96%B0"><span class="toc-number">1.3.1.</span> <span class="toc-text">主键索引更新</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%8D%E5%90%88%E7%B4%A2%E5%BC%95%E9%A1%BA%E5%BA%8F"><span class="toc-number">1.3.2.</span> <span class="toc-text">复合索引顺序</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%97%B6%E9%97%B4%E5%88%97%E7%B4%A2%E5%BC%95"><span class="toc-number">1.3.3.</span> <span class="toc-text">时间列索引</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%A1%88%E5%88%97"><span class="toc-number">1.4.</span> <span class="toc-text">案列</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#SQL%E6%94%B9%E5%86%99"><span class="toc-number">1.4.1.</span> <span class="toc-text">SQL改写</span></a></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E8%B0%83%E4%BC%98%E6%80%9D%E8%B7%AF2"><span class="toc-number">2.</span> <span class="toc-text">调优思路2</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#explain%E6%9C%89%E5%93%AA%E5%87%A0%E5%88%97%E9%87%8D%E8%A6%81"><span class="toc-number">2.1.</span> <span class="toc-text">explain有哪几列重要</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#mysql8%E5%89%8D%E6%8E%92%E9%99%A4%E7%BC%93%E5%AD%98"><span class="toc-number">2.2.</span> <span class="toc-text">mysql8前排除缓存</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%A1%8C%E6%95%B0%E5%AF%B9%E4%B8%8D%E5%AF%B9%EF%BC%8C%E7%94%A8analyze-table-t%E7%9F%AB%E6%AD%A3"><span class="toc-number">2.3.</span> <span class="toc-text">行数对不对，用analyze table t矫正</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%A6%86%E7%9B%96%E7%B4%A2%E5%BC%95-extra-using-index"><span class="toc-number">2.4.</span> <span class="toc-text">覆盖索引 extra using index</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%81%94%E5%90%88%E7%B4%A2%E5%BC%95-%E6%9C%80%E5%B7%A6%E5%8C%B9%E9%85%8D%E5%8E%9F%E5%88%99"><span class="toc-number">2.5.</span> <span class="toc-text">联合索引&#x2F;最左匹配原则</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E4%B8%8B%E6%8E%A8"><span class="toc-number">2.6.</span> <span class="toc-text">索引下推</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#changebuffer%E5%8F%AA%E6%9C%89%E6%99%AE%E9%80%9A%E7%B4%A2%E5%BC%95%E8%83%BD%E7%94%A8"><span class="toc-number">2.7.</span> <span class="toc-text">changebuffer只有普通索引能用</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%89%8D%E7%BC%80%E7%B4%A2%E5%BC%95"><span class="toc-number">2.8.</span> <span class="toc-text">前缀索引</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%BE%88%E9%95%BF%E7%9A%84%E5%AD%97%E6%AE%B5"><span class="toc-number">2.8.1.</span> <span class="toc-text">很长的字段</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%9D%A1%E4%BB%B6%E5%AD%97%E6%AE%B5%E5%87%BD%E6%95%B0-%E9%9A%90%E5%BC%8F%EF%BC%88%E7%B1%BB%E5%9E%8B%EF%BC%8C%E5%AD%97%E7%AC%A6%E7%BC%96%E7%A0%81%EF%BC%89%E8%BD%AC%E6%8D%A2%E6%93%8D%E4%BD%9C"><span class="toc-number">2.9.</span> <span class="toc-text">条件字段函数&#x2F;隐式（类型，字符编码）转换操作</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%A8%A1%E7%B3%8A%E5%8C%B9%E9%85%8D%E4%BC%98%E5%8C%96"><span class="toc-number">2.10.</span> <span class="toc-text">模糊匹配优化</span></a></li></ol></li></ol>
                </div>
            
        
        
            <h1 id="sql调优思路1"><a href="#sql调优思路1" class="headerlink" title="sql调优思路1"></a>sql调优思路1</h1><ol>
<li>优化表结构。</li>
<li>优化查询。从左到右。</li>
<li>索引优化</li>
</ol>
<h2 id="1-表结构检查"><a href="#1-表结构检查" class="headerlink" title="1.表结构检查"></a>1.表结构检查</h2><h3 id="表-amp-列名关键字（不区分大小写）"><a href="#表-amp-列名关键字（不区分大小写）" class="headerlink" title="表&amp;列名关键字（不区分大小写）"></a>表&amp;列名关键字（不区分大小写）</h3><p>在数据库设计建模阶段，对表名及字段名设置要合理，不能使用MySQL的关键字，如desc, order, status, group等。同时建议设置lower_case_table_names = 1表名不区分大小写。</p>
<h3 id="AUTO-INCREMENT属性"><a href="#AUTO-INCREMENT属性" class="headerlink" title="AUTO_INCREMENT属性"></a>AUTO_INCREMENT属性</h3><p>建表的时候主键id带有AUTO_INCREMENT属性，而且AUTO_INCREMENT=1，在InnoDB内部是通过一个系统全局变量dict_sys.row_id来计数，row_id是一个8字节的bigint unsigned，InnoDB在设计时只给row_id保留了6个字节的长度，这样row_id取值范围就是0到2^48 - 1，如果id的值达到了最大值，下一个值就从0开始继续循环递增，在代码中禁止指定主键id值插入。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">#新插入的id值会从10001开始，这是不对的，应该从1开始。</span><br><span class="line">create table booking( &#96;id&#96; bigint(20) NOT NULL AUTO_INCREMENT COMMENT &#39;主键id&#39;,......) engine &#x3D; InnoDB auto_increment &#x3D; 10000;</span><br><span class="line"></span><br><span class="line">#指定了id值插入，后续自增就会从该值开始+1，索引禁止指定id值插入。</span><br><span class="line">insert into booking(id, book_sn) values(1234551121, &#39;N12121&#39;);</span><br></pre></td></tr></table></figure>

<h3 id="NOT-NULL属性"><a href="#NOT-NULL属性" class="headerlink" title="NOT NULL属性"></a>NOT NULL属性</h3><p>根据业务含义，尽量将字段都添加上NOT NULL DEFAULT VALUE属性，如果列值存储了大量的NULL，会影响索引的稳定性。</p>
<h3 id="DEFAULT属性"><a href="#DEFAULT属性" class="headerlink" title="DEFAULT属性"></a>DEFAULT属性</h3><p>在创建表的时候，建议每个字段尽量都有默认值，禁止DEFAULT NULL，而是对字段类型填充响应的默认值。</p>
<h3 id="TEXT类型（varchar与char区别）"><a href="#TEXT类型（varchar与char区别）" class="headerlink" title="TEXT类型（varchar与char区别）"></a>TEXT类型（varchar与char区别）</h3><p>不建议使用Text数据类型，一方面由于传输大量的数据包可能会超过max_allowed_packet设置导致程序报错，另一方面表上的DML操作都会变的很慢，建议采用es或者对象存储OSS来存储和检索。</p>
<h2 id="2-优化查询"><a href="#2-优化查询" class="headerlink" title="2.优化查询"></a>2.优化查询</h2><h3 id="select检查（字段函数不要）"><a href="#select检查（字段函数不要）" class="headerlink" title="select检查（字段函数不要）"></a>select检查（字段函数不要）</h3><p><strong>自定义函数UDF</strong></p>
<p>SQL返回多少行，那么UDF函数就会被调用多少次，这是非常影响性能的。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#getOrderNo是用户自定义一个函数用户来根据order_sn来获取订单编号</span><br><span class="line">select id, payment_id, order_sn, getOrderNo(order_sn) from payment_transaction where status &#x3D; 1 and create_time between &#39;2020-10-01 10:00:00&#39; and &#39;2020-10-02 10:00:00&#39;;</span><br></pre></td></tr></table></figure>

<p><strong>text类型检查</strong></p>
<p>如果select出现text类型的字段，就会消耗大量的网络和IO带宽，由于返回的内容过大超过max_allowed_packet设置会导致程序报错，需要评估谨慎使用。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#表request_log的中content是text类型。</span><br><span class="line">select user_id, content, status, url, type from request_log where user_id &#x3D; 32121;</span><br></pre></td></tr></table></figure>

<p><strong>group_concat谨慎使用</strong></p>
<p>gorup_concat是一个字符串聚合函数，会影响SQL的响应时间，如果返回的值过大超过了max_allowed_packet设置会导致程序报错。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select batch_id, group_concat(name) from buffer_batch where status &#x3D; 0 and create_time between &#39;2020-10-01 10:00:00&#39; and &#39;2020-10-02 10:00:00&#39;;</span><br></pre></td></tr></table></figure>

<p><strong>内联子查询</strong></p>
<p>在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select id,(select rule_name from member_rule limit 1) as rule_name, member_id, member_type, member_name, status  from member_info m where status &#x3D; 1 and create_time between &#39;2020-09-02 10:00:00&#39; and &#39;2020-10-01 10:00:00&#39;;</span><br></pre></td></tr></table></figure>



<h3 id="from检查-inner-join"><a href="#from检查-inner-join" class="headerlink" title="from检查(inner join)"></a>from检查(inner join)</h3><p><strong>表的链接方式</strong></p>
<p>在MySQL中不建议使用Left Join，即使ON过滤条件列索引，一些情况也不会走索引，<strong>导致大量的数据行被扫描</strong>，SQL性能变得很差，同时要清楚ON和Where的区别。</p>
<p><strong>慎用left join语句,避免创建临时表</strong></p>
<p>left join时,on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,<strong>都会返回左边表中的记录</strong>。而where条件是在临时表生成好后,再对临时表进行过滤的条件，这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT a.member_id,a.create_time,b.active_time FROM operation_log a LEFT JOIN member_info b ON a.member_id &#x3D; b.member_id where  b.&#96;status&#96; &#x3D; 1</span><br><span class="line">and a.create_time between &#39;2020-10-01 00:00:00&#39; and &#39;2020-10-30 00:00:00&#39; limit 100, 0;</span><br></pre></td></tr></table></figure>

<p><strong>子查询</strong></p>
<p>由于MySQL的基于成本的优化器CBO对子查询的处理能力比较弱，不建议使用子查询，可以改写成Inner Join。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">select b.member_id,b.member_type, a.create_time,a.device_model from member_operation_log a inner join (select member_id,member_type from member_base_info where &#96;status&#96; &#x3D; 1</span><br><span class="line">and create_time between &#39;2020-10-01 00:00:00&#39; and &#39;2020-10-30 00:00:00&#39;) as b on a.member_id &#x3D; b.member_id;</span><br></pre></td></tr></table></figure>

<h3 id="where检查-避免运算-or；类型转换；字符集"><a href="#where检查-避免运算-or；类型转换；字符集" class="headerlink" title="where检查(避免运算, or；类型转换；字符集)"></a>where检查(避免运算, or；类型转换；字符集)</h3><p><strong>索引列被运算</strong></p>
<p>当一个字段被索引，同时出现where条件后面，是不能进行任何运算，会导致索引失效。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#device_no列上有索引，由于使用了ltrim函数导致索引失效</span><br><span class="line">select id, name , phone, address, device_no from users where ltrim(device_no) &#x3D; &#39;Hfs1212121&#39;;</span><br><span class="line">#balance列有索引,由于做了运算导致索引失效</span><br><span class="line">select account_no, balance from accounts where balance + 100 &#x3D; 10000 and status &#x3D; 1;</span><br></pre></td></tr></table></figure>

<p><strong>类型转换</strong></p>
<p>对于Int类型的字段，传varchar类型的值是可以走索引，MySQL内部自动做了隐式类型转换；相反对于varchar类型字段传入Int值是无法走索引的，应该做到对应的字段类型传对应的值总是对的。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#user_id是bigint类型，传入varchar值发生了隐式类型转换，可以走索引。</span><br><span class="line">select id, name , phone, address, device_no from users where user_id &#x3D; &#39;23126&#39;;</span><br><span class="line">#card_no是varchar(20)，传入int值是无法走索引</span><br><span class="line">select id, name , phone, address, device_no from users where card_no &#x3D; 2312612121;</span><br></pre></td></tr></table></figure>

<p><strong>列字符集</strong></p>
<p>从MySQL 5.6开始建议所有对象字符集应该使用用utf8mb4，包括MySQL实例字符集，数据库字符集，表字符集，列字符集。避免在关联查询Join时字段字符集不匹配导致索引失效，同时目前只有utf8mb4支持emoji表情存储。</p>
<p><strong>谨防where子句中的OR</strong>。where语句使用or，且没有使用覆盖索引,会进行全表扫描。应该尽量避免这样OR语句。尽量使用UNION代替OR</p>
<h3 id="group-by检查（前缀索引，函数运算不消除排序）"><a href="#group-by检查（前缀索引，函数运算不消除排序）" class="headerlink" title="group by检查（前缀索引，函数运算不消除排序）"></a>group by检查（前缀索引，函数运算不消除排序）</h3><p><strong>前缀索引</strong></p>
<p>group by后面的列有索引，索引可以消除排序带来的CPU开销，如果是前缀索引，是不能消除排序的。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#device_no字段类型varchar(200)，创建了前缀索引。</span><br><span class="line">mysql&gt; alter table users add index idx_device_no(device_no(64));</span><br><span class="line"></span><br><span class="line">mysql&gt; select device_no, count(*) from users where create_time between &#39;2020-10-01 00:00:00&#39; and &#39;2020-10-30 00:00:00&#39; group by device_no;</span><br></pre></td></tr></table></figure>

<p><strong>函数运算</strong></p>
<p>假设需要统计某月每天的新增用户量，参考如下SQL语句，虽然可以走create_time的索引，但是不能消除排序，可以考虑冗余一个字段stats_date date类型来解决这种问题。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select DATE_FORMAT(create_time, &#39;%Y-%m-%d&#39;), count(*) from users where create_time between &#39;2020-09-01 00:00:00&#39; and &#39;2020-09-30 23:59:59&#39; group by DATE_FORMAT(create_time, &#39;%Y-%m-%d&#39;);</span><br></pre></td></tr></table></figure>



<h3 id="order-by检查（前缀索引，要用索引列）"><a href="#order-by检查（前缀索引，要用索引列）" class="headerlink" title="order by检查（前缀索引，要用索引列）"></a>order by检查（前缀索引，要用索引列）</h3><p><strong>前缀索引</strong></p>
<p>order by后面的列有索引，索引可以消除排序带来的CPU开销，如果是前缀索引，是不能消除排序的。</p>
<p>表中有个index(cat_id, shop_price)联合索引。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">where cat_id&#x3D;N order by shop_price ,可以利用索引来排序,</span><br><span class="line"></span><br><span class="line">select goods_id,cat_id,shop_price from goods order by shop_price;</span><br><span class="line"></span><br><span class="line">&#x2F;&#x2F; using where,按照shop_price索引取出的结果,本身就是有序的.</span><br><span class="line"></span><br><span class="line"> </span><br><span class="line"></span><br><span class="line">select goods_id,cat_id,shop_price from goods order by click_count;</span><br><span class="line"></span><br><span class="line">&#x2F;&#x2F; using filesort 用到了文件排序,即取出的结果再次排序</span><br></pre></td></tr></table></figure>



<h3 id="limit检查（利用好主键）"><a href="#limit检查（利用好主键）" class="headerlink" title="limit检查（利用好主键）"></a>limit检查（利用好主键）</h3><p><strong>limit m,n要慎重</strong></p>
<p>对于limit m, n分页查询，越往后面翻页即m越大的情况下SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。</p>
<h2 id="3-索引优化（explain）"><a href="#3-索引优化（explain）" class="headerlink" title="3.索引优化（explain）"></a>3.索引优化（explain）</h2><h3 id="主键索引更新"><a href="#主键索引更新" class="headerlink" title="主键索引更新"></a>主键索引更新</h3><p>在MySQL中尽量所有的update都使用主键id去更新，因为id是聚集索引存储着整行数据，不需要回表，性能是最高的。</p>
<h3 id="复合索引顺序"><a href="#复合索引顺序" class="headerlink" title="复合索引顺序"></a>复合索引顺序</h3><h3 id="时间列索引"><a href="#时间列索引" class="headerlink" title="时间列索引"></a>时间列索引</h3><h2 id="案列"><a href="#案列" class="headerlink" title="案列"></a>案列</h2><p>#原始SQL，频繁访问的接口，目前执行时间2s。<br>select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and app_id is not null) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;</p>
<p>#执行计划<br>+—-+————-+————–+——-+———————————+————+———+——+——+————-+<br>| id | select_type | table        | type  | possible_keys                   | key        | key_len | ref  | rows | Extra       |<br>+—-+————-+————–+——-+———————————+————+———+——+——+————-+<br>|  1 | SIMPLE      | t_user_msg | index | invite_id,app_id,team_id | created_time | 5       | NULL |   10 | Using where |<br>+—-+————-+————–+——-+———————————+————+———+——+——+————-+<br>1 row in set (0.00 sec)</p>
<p>表上有单列索引invite_id,app_id,team_id,created_time，走的是create_time的索引，而且type=index索引全扫描，因为create_time没有出现在where条件后，只出现在order by后面，只能是type=index，这也预示着表数据量越大该SQL越慢，我们期望是走三个单列索引invite_id，app_id，team_id，然后type=index_merge操作。</p>
<p>按照常规思路，对于OR条件拆分两部分，分别进行分析。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select id, ……. from t_user_msg where 1 and  **(team_id in (3212) and app_id is not null)** order by created_time desc limit 0,10;</span><br></pre></td></tr></table></figure>

<p>从执行计划看走的是team_id的索引，没有问题。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">| id | select_type | table        | type | possible_keys        | key     | key_len | ref   | rows | Extra                       |</span><br><span class="line">+----+-------------+--------------+------+----------------------+---------+---------+-------+------+-----------------------------+</span><br><span class="line">|  1 | SIMPLE      | t_user_msg | ref  | app_id,team_id | team_id | 8       | const |   30 | Using where; Using filesort |</span><br></pre></td></tr></table></figure>

<p>再看另外一个sql语句：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select id, ……. from t_user_msg where 1 and  **(invite_id&#x3D;12395 or app_id&#x3D;12395)** order by created_time desc limit 0,10;</span><br></pre></td></tr></table></figure>

<p>从执行计划上看，分别走的是invite_id,app_id的单列索引，同时做了index_merge合并操作，也没有问题。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">| id | select_type | table        | type        | possible_keys           | key                     | key_len | ref  | rows | Extra                                                             |</span><br><span class="line">+----+-------------+--------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------------------+</span><br><span class="line">|  1 | SIMPLE      | t_user_msg | index_merge | invite_id,app_id | invite_id,app_id | 9,9     | NULL |    2 | Using union(invite_id,app_id); Using where; Using filesort |</span><br></pre></td></tr></table></figure>

<p><strong>通过上面的分析，第一部分SQL走的执行计划走team_id索引没问题，第二部分SQL分别走invite_id,app_id索引并且index_merge也没问题，为什么两部分SQL进行OR关联之后走create_time的单列索引呢，不应该是三个单列索引的index_merge吗？</strong></p>
<p><strong>index_merge默认是在优化器选项是开启的，主要是将多个范围扫描的结果集合并成一个，可以通过变量查看。</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql &gt;select @@optimizer_switch;</span><br><span class="line">| index_merge&#x3D;on,index_merge_union&#x3D;on,index_merge_sort_union&#x3D;on,index_merge_intersection&#x3D;on,</span><br></pre></td></tr></table></figure>

<p>其他三个字段都传入的是具体的值，而且都走了相应的索引，只能怀疑app_id is not null这个条件影响了CBO对最终执行计划的选择，去掉这个条件来看执行计划，竟然走了三个单列索引且type=index_merge，那下面只要搞定<strong>app_id is not null</strong>这个条件就OK了吧。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">| id | select_type | table        | type        | possible_keys                   | key                             | key_len | ref  | rows | Extra                                                                     |</span><br><span class="line">+----+-------------+--------------+-------------+---------------------------------+---------------------------------+---------+------+------+---------------------------------------------------------------------------+</span><br><span class="line">|  1 | SIMPLE      | t_user_msg | index_merge | invite_id,app_id,teadm_id | team_id,invite_id,app_id | 8,9,9   | NULL |   32 | Using union(team_id,invite_id,app_id); Using where; Using filesort |</span><br></pre></td></tr></table></figure>

<h3 id="SQL改写"><a href="#SQL改写" class="headerlink" title="SQL改写"></a>SQL改写</h3><p>通过上面分析得知，条件app_id is not null影响了CBO的选择，下面进行改造。</p>
<p><strong>改写优化1</strong></p>
<p>根据SQL开发规范改写，将OR改写成Union All方式即可，最终的SQL如下：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select id, ……. from (</span><br><span class="line">select id, ……. from t_user_msg where **1 and (club_id in (5821) and applicant_id is not null)**</span><br><span class="line">        **union all** select id, ……. from t_user_msg where **1 and invitee_id&#x3D;&#39;146737&#39;**</span><br><span class="line">        **union all** select id, ……. from  t_user_msg where **1 and app_id&#x3D;&#39;146737&#39;**</span><br><span class="line">       ) as a order by created_time desc limit 0,10;</span><br></pre></td></tr></table></figure>

<p>一般情况下，Java代码和SQL是分开的，SQL是配置在xml文件中，根据业务需求，除了team_id是必填，其他两个都是可选的，所以这种改写虽然能提高SQL执行效率，但不适合这种业务场景。</p>
<p><strong>改写优化2</strong></p>
<p>app_id is not null 改写为**IFNULL(app_id, 0) &gt;0)**，最终的SQL为：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **IFNULL(app_id, 0) &gt;0)**) or (invite_id&#x3D;12395 or app_id&#x3D;12395) order by created_time desc limit 0,10;</span><br></pre></td></tr></table></figure>

<p><strong>改写优化3</strong></p>
<p>将字段app_id bigint(20) DEFAULT NULL，变更为app_id bigint(20) <strong>NOT NULL DEFAULT 0</strong>，同时更新将app_id is null的时候全部更新成0，就可以将条件app_id is not null 转换为app_id &gt; 0，最终的SQL为：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select id,team_id,reason,status,type,created_at,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **app_id &gt; 0)**) or (invite_id&#x3D;12395 or app_id&#x3D;12395) order by created_time desc limit 0,10;</span><br></pre></td></tr></table></figure>

<p>从执行计划看，两种改写优化方式都走三个单列索引，执行时间从2s降低至10ms，线上采用的是<strong>优化1</strong>的方式，如果一开始能遵循MySQL开发规范就就会避免问题的发生。</p>
<h1 id="调优思路2"><a href="#调优思路2" class="headerlink" title="调优思路2"></a>调优思路2</h1><p><img src="/gitee-internal-blog/images/Mysql-%E8%B0%83%E4%BC%98%E8%84%91%E5%9B%BE.jpg" alt="image-20210925163806946"></p>
<h2 id="explain有哪几列重要"><a href="#explain有哪几列重要" class="headerlink" title="explain有哪几列重要"></a>explain有哪几列重要</h2><p>type（走的是什么类别，all和index都是全表）, extra(using index索引覆盖), rows（多少行）</p>
<h2 id="mysql8前排除缓存"><a href="#mysql8前排除缓存" class="headerlink" title="mysql8前排除缓存"></a>mysql8前排除缓存</h2><p>我们在执行SQL的时候，记得加上SQL NoCache去跑SQL，这样跑出来的时间就是真实的查询时间了。缓存失效比较频繁的原因就是，只要我们一对表进行更新，那这个表所有的缓存都会被清空，其实我们很少存在不更新的表，特别是我之前的电商场景，可能静态表可以用到缓存，但是我们都走大数据离线分析，缓存也就没用了。</p>
<h2 id="行数对不对，用analyze-table-t矫正"><a href="#行数对不对，用analyze-table-t矫正" class="headerlink" title="行数对不对，用analyze table t矫正"></a>行数对不对，用analyze table t矫正</h2><p>MySQL中数据的单位都是页，MySQL又采用了采样统计的方法，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。</p>
<p>至于MySQL索引可能走错也很好理解，如果走A索引要扫描100行，B所有只要20行，但是他可能选择走A索引，你可能会想MySQL是不是有病啊，其实不是的。</p>
<p>一般走错都是因为优化器在选择的时候发现，走A索引没有额外的代价，比如走B索引并不能直接拿到我们的值，还需要回到主键索引才可以拿到，多了一次回表的过程，这个也是会被优化器考虑进去的。</p>
<h2 id="覆盖索引-extra-using-index"><a href="#覆盖索引-extra-using-index" class="headerlink" title="覆盖索引 extra using index"></a>覆盖索引 extra using index</h2><p>如果在我们建立的索引上就已经有我们需要的字段，就不需要回表了</p>
<h2 id="联合索引-最左匹配原则"><a href="#联合索引-最左匹配原则" class="headerlink" title="联合索引/最左匹配原则"></a>联合索引/最左匹配原则</h2><h2 id="索引下推"><a href="#索引下推" class="headerlink" title="索引下推"></a>索引下推</h2><p>在MySQL 5.6之前，只能从ID1开始一个个回表，到主键索引上找出数据行，再对比字段值。</p>
<p>而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。</p>
<p>我们执行查询<code>explain SELECT * from user where age &gt;10 and name = &#39;a&#39;</code>，如下图所示，就会看见<code>Extra</code>中显示了<code>Using index condition</code>，你可能就知道了，这表示出现了<strong>索引下推</strong>了。</p>
<p>现在我们基本都使用的5.6以上的版本了，默认就是开启ICP的，想关闭的话可以通过命令<code>SET optimizer_switch = &#39;index_condition_pushdown=off&#39;;</code>。</p>
<h2 id="changebuffer只有普通索引能用"><a href="#changebuffer只有普通索引能用" class="headerlink" title="changebuffer只有普通索引能用"></a>changebuffer只有普通索引能用</h2><p>对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。</p>
<p>要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。</p>
<p>因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。</p>
<p>将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一，change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。</p>
<h2 id="前缀索引"><a href="#前缀索引" class="headerlink" title="前缀索引"></a>前缀索引</h2><p>覆盖索引是不需要回表的，但是前缀索引，即使你的联合索引已经包涵了相关信息，他还是会回表，因为他不确定你到底是不是一个完整的信息，就算你是<a target="_blank" rel="noopener" href="http://www.aobing@mogu.com一个完整的邮箱去查询,他还是不知道你是否是完整的,所以他需要回表去判断一下./">www.aobing@mogu.com一个完整的邮箱去查询，他还是不知道你是否是完整的，所以他需要回表去判断一下。</a></p>
<h3 id="很长的字段"><a href="#很长的字段" class="headerlink" title="很长的字段"></a>很长的字段</h3><p>因为存在一个磁盘占用的问题，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。</p>
<p>把字段hash为另外一个字段存起来，每次校验hash就好了，hash的索引也不大。</p>
<p><strong>采用倒序，或者删减字符串这样的情况去建立我们自己的区分度</strong></p>
<p>不过大家需要注意的是，调用函数也是一次开销哟，这点当时没注意。</p>
<p>就比如本来是<a target="_blank" rel="noopener" href="http://www.aobing@qq,com/">www.aobing@qq,com</a> 其实前面的<code>www.</code>基本上是没任何区分度的，所有人的邮箱都是这么开头的，你一搜一大堆出来，放在索引还浪费内存，你可以substring()函数截取掉前面的，然后建立索引。</p>
<p>我们所有人的身份证都是区域开头的，同区域的人很多，那怎么做良好的区分呢？REVERSE（）函数翻转一下，区分度可能就高了。</p>
<p>这些操作都用到了函数，我就说一下函数的坑。</p>
<h2 id="条件字段函数-隐式（类型，字符编码）转换操作"><a href="#条件字段函数-隐式（类型，字符编码）转换操作" class="headerlink" title="条件字段函数/隐式（类型，字符编码）转换操作"></a>条件字段函数/隐式（类型，字符编码）转换操作</h2><p>对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。比如 select * from tradelog where id + 1 = 10000 就走不上索引，select * from tradelog where id = 9999就可以。</p>
<h2 id="模糊匹配优化"><a href="#模糊匹配优化" class="headerlink" title="模糊匹配优化"></a>模糊匹配优化</h2><p>对于where条件后的 <code>like &#39;%xxx&#39;</code> 是无法利用索引扫描，可以利用MySQL 5.7的生成列模拟函数索引的方式解决，具体步骤如下：</p>
<ol>
<li><strong>利用内置reverse函数将like ‘%风云’反转为like ‘云风%’，基于此函数添加虚拟生成列。</strong></li>
<li><strong>在虚拟生成列上创建索引。</strong></li>
<li><strong>将SQL改写成通过生成列like reverse(‘%风云’)去过滤，走生成列上的索引。</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span><span class="keyword">alter</span> <span class="keyword">table</span> users01 <span class="keyword">add</span> reverse_nickname <span class="type">varchar</span>(<span class="number">200</span>) generated always <span class="keyword">as</span> (reverse(nickname));</span><br><span class="line"></span><br><span class="line">mysql<span class="operator">&gt;</span><span class="keyword">alter</span> <span class="keyword">table</span> users01 <span class="keyword">add</span> index idx_reverse_nickname(reverse_nickname);</span><br></pre></td></tr></table></figure>


            </div>
        
        <footer class="article-footer">
        </footer>
    </div>
</article>


    
<nav id="article-nav">
    
        <a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/3-mysql-sql%E6%89%A7%E8%A1%8C%E8%BF%87%E7%A8%8B%E5%AE%8F%E8%A7%82%E6%B5%81%E7%A8%8B-redo-undo-bin/" id="article-nav-newer" class="article-nav-link-wrap">
            <strong class="article-nav-caption">Newer</strong>
            <div class="article-nav-title">
                
                    3-mysql-sql执行过程宏观流程-redo-undo-bin
                
            </div>
        </a>
    
    
        <a href="/gitee-internal-blog/2021/09/04/%E6%95%B0%E6%8D%AE%E5%BA%93/mysql/4-mysql-explain%E6%89%A7%E8%A1%8C%E5%85%B7%E4%BD%93%E6%B5%81%E7%A8%8B/" id="article-nav-older" class="article-nav-link-wrap">
            <strong class="article-nav-caption">Older</strong>
            <div class="article-nav-title">4-mysql-explain执行具体流程</div>
        </a>
    
</nav>





    
    




<!-- baidu url auto push script -->
<script type="text/javascript">
    !function(){var e=/([http|https]:\/\/[a-zA-Z0-9\_\.]+\.baidu\.com)/gi,r=window.location.href,o=document.referrer;if(!e.test(r)){var n="//api.share.baidu.com/s.gif";o?(n+="?r="+encodeURIComponent(document.referrer),r&&(n+="&l="+r)):r&&(n+="?l="+r);var t=new Image;t.src=n}}(window);
</script>     
</section>
        </div>
        <footer id="footer">
    <div class="outer">
        <div id="footer-info" class="inner">
            Johnny &copy; 2023 
            <a rel="license noopener" target="_blank" href="http://creativecommons.org/licenses/by-nc-nd/4.0/"><img alt="Creative Commons License" style="border-width:0" src="https://i.creativecommons.org/l/by-nc-nd/4.0/80x15.png" /></a>
            <br> Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>. Theme - <a target="_blank" rel="noopener" href="https://github.com/zthxxx/hexo-theme-Wikitten">wikitten</a>
            
                <br>
                <span id="busuanzi_container_site_pv"><i class="fa fa-eye"></i> <span id="busuanzi_value_site_pv"></span></span>
                &nbsp;|&nbsp;
                <span id="busuanzi_container_site_pv"><i class="fa fa-user"></i> <span id="busuanzi_value_site_uv"></span></span>
            
        </div>
    </div>
</footer>

        

    
        
<script src="/gitee-internal-blog/libs/lightgallery/js/lightgallery.min.js"></script>

        
<script src="/gitee-internal-blog/libs/lightgallery/js/lg-thumbnail.min.js"></script>

        
<script src="/gitee-internal-blog/libs/lightgallery/js/lg-pager.min.js"></script>

        
<script src="/gitee-internal-blog/libs/lightgallery/js/lg-autoplay.min.js"></script>

        
<script src="/gitee-internal-blog/libs/lightgallery/js/lg-fullscreen.min.js"></script>

        
<script src="/gitee-internal-blog/libs/lightgallery/js/lg-zoom.min.js"></script>

        
<script src="/gitee-internal-blog/libs/lightgallery/js/lg-hash.min.js"></script>

        
<script src="/gitee-internal-blog/libs/lightgallery/js/lg-share.min.js"></script>

        
<script src="/gitee-internal-blog/libs/lightgallery/js/lg-video.min.js"></script>

    
    
        
<script src="/gitee-internal-blog/libs/justified-gallery/jquery.justifiedGallery.min.js"></script>

    
    
        <script type="text/x-mathjax-config">
    MathJax.Hub.Config({
        tex2jax: {
            inlineMath: [ ["$","$"], ["\\(","\\)"] ],
            skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code'],
            processEscapes: true,
            TeX: {
                equationNumbers: {
                  autoNumber: 'AMS'
                }
            }
        }
    });
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax();
        for (var i = 0; i < all.length; ++i)
            all[i].SourceElement().parentNode.className += ' has-jax';
    });
</script>
<script async src="//cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>
    



<!-- Custom Scripts -->

<script src="/gitee-internal-blog/js/main.js"></script>


    </div>
</body>
</html>